/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package databaseproject;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Date;
import java.util.Scanner;

/**
 *
 * @author chris
 */
public class Band {
    private static Connection con;
    private static String username;
    private static boolean userIsAdmin;
    
    public static void doMenu(Connection nCon, String nUsername, boolean nUserIsAdmin)
    {
        con = nCon;
        username = nUsername;
        userIsAdmin = nUserIsAdmin;
        
        boolean done = false;

        while (!done)
        {
            Menu menu = new Menu();
            menu.setTitle("Bands");
            menu.addOption("List Bands");
            if (userIsAdmin)
            {
                menu.addOption("Add Band");
                menu.addOption("Edit Band");
                
            }
            else
            {
                menu.addOption("List Bands I Like");
            }
            switch (menu.display())
            {
                case (1):
                    Menu listMenu = new Menu();
                    listMenu.addOptionsFromSQLQuery(con, "select bname from BAND");
                    int choice = listMenu.display();
                    displayDetail(listMenu.getOptionAt(choice));
                    break;
                    
                case (2):
                    if(!userIsAdmin){
                        bandLikes();
                        break;
                    }
                    
                    Scanner input = new Scanner(System.in);
                    System.out.print("Band Name: ");
                    String bname = input.nextLine();
                    System.out.print("Formation Year: ");
                    int fyear = input.nextInt();
                    System.out.print("          Month: ");
                    int fmonth = input.nextInt();
                    System.out.print("          Day: ");
                    int fday = input.nextInt();
                    String form = String.format("%04d-%02d-%02d", fyear, fmonth, fday);
                    
                    System.out.print("Still Active? (y/n): ");
                    char active = input.next().charAt(0);
                    
                    if (active=='y'){
                        try
                        {
                            PreparedStatement st = con.prepareStatement("insert into BAND values (?,?,null)");
                            st.setString(1, bname);
                            st.setString(2, form);
                            st.executeUpdate();
                        }
                        catch (java.sql.SQLException e)
                        {
                            System.out.println("SQL failed in add band." + e);
                        }
                    }
                    
                    else{    
                        System.out.print("Disband Year: ");
                        int dyear = input.nextInt();
                        System.out.print("        Month: ");
                        int dmonth = input.nextInt();
                        System.out.print("        Day: ");
                        int dday = input.nextInt();
                        String disband = String.format("%04d-%02d-%02d", dyear, dmonth, dday);



                        try
                        {
                            PreparedStatement st = con.prepareStatement("insert into BAND values (?,?,?)");
                            st.setString(1, bname);
                            st.setString(2, form);
                            st.setString(3, disband);
                            st.executeUpdate();
                        }
                        catch (java.sql.SQLException e)
                        {
                            System.out.println("SQL failed in add band." + e);

                        }
                    }
                    break;
                    
                case(3):
                    if(userIsAdmin)
                        editBand();
                    else
                        done = true;
                    break;
                    
                  
                
                default:
                    done = true;
                    break;
            }
        }
    }
    
    public static void displayDetail(String selection)
    {
        try
        {
            boolean disbanded = false;
            // Display general band info
            PreparedStatement stmt = con.prepareStatement("select * from BAND where bname=?",
                                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_READ_ONLY);
            stmt.setString(1, selection);
            ResultSet rs = stmt.executeQuery();


            rs.first();
            
            System.out.printf("Band Name: %s\n",rs.getString("BNAME"));
            System.out.printf("Date Formed: %s\n",rs.getDate("DATE_FORMED"));
            if (rs.getDate("DATE_DISBANDED") != null)
            {
                System.out.printf("Date Disbanded: %s\n", rs.getDate("DATE_DISBANDED"));
                disbanded = true;
            }
            
            // Display current members
            PreparedStatement stmt2 = con.prepareStatement("select PLAYS_IN.MNAME,"
                    + "date_joined,date_left,instrument from BAND join PLAYS_IN"
                    + " on BAND.BNAME=PLAYS_IN.BNAME join INSTRUMENT on "
                    + "PLAYS_IN.MNAME=INSTRUMENT.MNAME where DATE_LEFT is null "
                    + "and BAND.BNAME=?",
                                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_READ_ONLY);
            stmt2.setString(1, selection);
            rs = stmt2.executeQuery();

            if (rs.first() && !disbanded)
            {
                System.out.println("Current Members:");
                do
                {
                    if (rs.getString("instrument") != null)
                        System.out.printf("  %s - %s (joined %s)\n", rs.getString("mname"), rs.getString("instrument"), rs.getDate("DATE_JOINED"));
                    else
                        System.out.printf("  %s (joined %s)\n", rs.getString("mname"), rs.getDate("DATE_JOINED"));
                    
                } while (rs.next());
            }
            
            // Display former members
            
            PreparedStatement stmt3 = con.prepareStatement("select PLAYS_IN.MNAME,"
                    + "date_joined,date_left,instrument from BAND join PLAYS_IN "
                    + "on BAND.BNAME=PLAYS_IN.BNAME join INSTRUMENT on PLAYS_IN.MNAME="
                    + "INSTRUMENT.MNAME where DATE_LEFT is not null and BAND.BNAME=?",
                                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_READ_ONLY);
            stmt3.setString(1, selection);
            rs = stmt3.executeQuery();

            if (rs.first())
            {
                if (disbanded)
                    System.out.println("Members:");
                else
                    System.out.println("Former Members:");
                do
                {
                    if (rs.getString("instrument") != null)
                        System.out.printf("  %s - %s (joined %s, left %s)\n", rs.getString("mname"), rs.getString("instrument"), rs.getDate("DATE_JOINED"), rs.getDate("DATE_LEFT"));
                    else
                        System.out.printf("  %s (joined %s, left %s)\n", rs.getString("mname"), rs.getDate("DATE_JOINED"), rs.getDate("DATE_LEFT"));
                    
                } while (rs.next());
            }
        }
        catch (java.sql.SQLException e)
        {
            System.out.print(e);
            return;
        }
        
    }
    public static void bandLikes()
    {
        boolean done = false;
        
        while(!done)
        {
            try{
                Menu menu = new Menu();
                menu.addOption("Add A Band");
                menu.addOption("Remove A Band");

                PreparedStatement stmt = con.prepareStatement("select * from \"USER\" natural "
                        + "join likes where username=?",
                                            ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_READ_ONLY);
                stmt.setString(1, username);
                ResultSet rs = stmt.executeQuery();

                if(rs.first())
                {
                    System.out.println("\nYou Like:");
                    do{
                        System.out.println(rs.getString("BNAME"));

                    }while(rs.next());
                }
                else
                {
                    System.out.println("\nYou Like No Bands.");
                }


                switch(menu.display())
                {
                    case (1):
                        Menu add = new Menu();
                        add.setTitle("Select A Band That You Like:");
                        add.addOptionsFromSQLQuery(con, "select bname from band "
                                + "where not exists (select bname from \"USER\" "
                                + "natural join likes where username = '"+username+"' "
                                + "and band.bname = likes.bname)");
                        
                        int bandToAdd = add.display();
                        if(bandToAdd<add.getBackOption())
                        {
                            stmt.executeUpdate("insert into likes values('"+
                                    username+"','"+add.getOptionAt(bandToAdd) +"')");
                        }
                        break;

                    case (2):
                        Menu delete = new Menu();
                        delete.setTitle("Select A Band That You Don't Like:");
                        delete.addOptionsFromSQLQuery(con, "select bname from \"USER\" "
                                + "natural join likes where username = '"+username+"'");
                        
                        int bandToDelete = delete.display();
                        if(bandToDelete<delete.getBackOption())
                        {
                            stmt.executeUpdate("delete from likes where username"
                                    + " = '"+username+"' and bname = '"
                                    +delete.getOptionAt(bandToDelete) +"'");
                        }
                        break;

                    default:
                        done = true;
                        break;
                }
            }
            catch (java.sql.SQLException e)
            {
                return;
            }
        }
        
    }
    public static void editBand()
    {
        Scanner input = new Scanner(System.in);
        Menu edit = new Menu();
        edit.addOptionsFromSQLQuery(con, "select bname from BAND");
        edit.setTitle("Select A Band to Edit");
        int choice = edit.display();
        
        if(choice<edit.getBackOption())
        {
            boolean done = false;
            while(!done)
            {
                String band = edit.getOptionAt(choice);
                displayDetail(band);
                Menu menu = new Menu();
                menu.addOption("Add Member");

                
                switch(menu.display())
                {
                    case(1):
                        Menu add = new Menu();
                        add.addOptionsFromSQLQuery(con, "Select mname from Musician");
                        add.setTitle("Select Musician to Add to " + band);
                        
                        int musChoice = add.display();
                        String musician = add.getOptionAt(musChoice);
                        
                        System.out.println("Input Date Joined (Year): ");
                        int year = input.nextInt();
                        System.out.println("                 (Month): ");
                        int month = input.nextInt();
                        System.out.println("                   (Day): ");
                        int day = input.nextInt();
                        String date = String.format("%04d-%02d-%02d", year, month, day);
                        
                        try{
                            PreparedStatement st = con.prepareStatement("insert into PLAYS_IN"
                                    + " values (?,?,?,null)");
                            st.setString(1, band);
                            st.setString(2, musician);
                            st.setString(3, date);
                            st.executeUpdate();
                        }
                        catch (java.sql.SQLException e)
                        {
                            System.out.print(e);
                            return;
                        }
                        
                        break;
                        

                        

                        
                    default:
                        done = true;
                        break;
                }
            }
            
            
        }
    }
}
